﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Threading;
using System.IO;

namespace Observer
{
    public partial class Form1 : Form
    {
        string _connectionString;
        DBConnect _db;
        String _filename = "export.xls";
        Boolean _allowEdit = false;
        String _barangEdit;
        int _rowEdit;

        public Form1()
        {
            InitializeComponent();
            _db = new DBConnect(textBox1.Text, "admin");
            MySqlCommand _command = _db.OpenConnection().CreateCommand();
            setCabang(_command);
            _connectionString = _db.getDBString();
            //updateVersion();
        }
        private void updateVersion()
        {
            try
            {
                MySqlCommand _command = _db.OpenConnection().CreateCommand();
                _command.CommandText = "REPLACE INTO version SET Version = '" + this.Text + "'";
                _command.Prepare();
                _command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (_db.getConnection().State == ConnectionState.Open)
                {
                    _db.CloseConnection();
                }
            }
        }
        private DataTable SQLCommand(MySqlCommand _cmd, string _txt)
        {
            _cmd.CommandText = _txt;
            DataSet _ds = new DataSet();
            MySqlDataAdapter _adap = new MySqlDataAdapter(_cmd);
            _adap.Fill(_ds);
            DataTable myDataTable = _ds.Tables[0];
            return myDataTable;
        }

        private void setCabang(MySqlCommand _cmd)
        {
            DataTable _dt = new DataTable();
            _dt = SQLCommand(_cmd, "SELECT Cabang FROM cabang WHERE Cabang != '' ORDER BY Cabang");
            comboBox1.DisplayMember = "Cabang";
            comboBox1.DataSource = _dt;
            _db.CloseConnection();
        }

        private void showRekap(MySqlCommand _cmd)
        {
            try
            {
                DataTable _dt = new DataTable();
                _dt = SQLCommand(_cmd, "SELECT Date, Barcode, Nama, daftar_warna.Warna, Masuk, Pindah, Keluar, Dari, Ke FROM log_stok INNER JOIN barcode_barang ON log_stok.Barang = barcode_barang.Barcode INNER JOIN list_barang ON barcode_barang.Barang = list_barang.Kode INNER JOIN daftar_warna ON barcode_barang.Warna = daftar_warna.Kode ORDER BY Date");
                dataGridView1.DataSource = _dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void showStok(MySqlCommand _cmd)
        {
            try
            {
                DataTable _dt = new DataTable();
                _dt = SQLCommand(_cmd, "SELECT lokasi_barang.Barang, list_barang.Nama, daftar_warna.Warna, SUM(Jumlah) AS Total FROM lokasi_barang INNER JOIN barcode_barang ON lokasi_barang.Barang = barcode_barang.Barcode INNER JOIN list_barang ON barcode_barang.Barang = list_barang.Kode INNER JOIN daftar_warna ON barcode_barang.Warna = daftar_warna.Kode GROUP BY lokasi_barang.Barang");
                dataGridView1.DataSource = _dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void showLevel(MySqlCommand _cmd)
        {
            try
            {
                DataTable _dt = new DataTable();
                _dt = SQLCommand(_cmd, "SELECT * FROM (SELECT lokasi_barang.Barang, list_barang.Nama, daftar_warna.Warna, SUM(Jumlah) AS Total, batas_aman AS 'Batas Aman', IF(batas_aman IS NULL, '0', '1') AS '< 100%', IF(SUM(Jumlah) >= batas_aman, '1', '0') AS '100%', IF(SUM(Jumlah) >= 3 * batas_aman / 2, '1', '0') AS '150%', IF(SUM(Jumlah) >= batas_aman * 2, '1', '0') AS '200%', IF(SUM(Jumlah) >= 5 * batas_aman / 2, '1', '0') AS '250%', IF(SUM(Jumlah) >= batas_aman * 3, '1', '0') AS '> 300%' FROM lokasi_barang INNER JOIN barcode_barang ON lokasi_barang.Barang = barcode_barang.Barcode INNER JOIN list_barang ON barcode_barang.Barang = list_barang.Kode INNER JOIN daftar_warna ON barcode_barang.Warna = daftar_warna.Kode LEFT JOIN severity_level ON severity_level.barang = barcode_barang.Barcode GROUP BY lokasi_barang.Barang) AS temp ORDER BY '< 100%', '100%', '150%', '200%', '250%', '300%'");
                dataGridView1.DataSource = _dt;
                changeLevelColour();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void changeLevelColour()
        {
            DataGridViewRow _tempRow = new DataGridViewRow();
            DataGridViewColumn _tempColumn = new DataGridViewColumn();
            for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
            {
                _tempRow = dataGridView1.Rows[i];
                _tempColumn = dataGridView1.Columns["< 100%"];
                _tempColumn.Width = 30;
                _tempColumn = dataGridView1.Columns["100%"];
                _tempColumn.Width = 30;
                _tempColumn = dataGridView1.Columns["150%"];
                _tempColumn.Width = 30;
                _tempColumn = dataGridView1.Columns["200%"];
                _tempColumn.Width = 30;
                _tempColumn = dataGridView1.Columns["250%"];
                _tempColumn.Width = 30;
                _tempColumn = dataGridView1.Columns["> 300%"];
                _tempColumn.Width = 30;
                _tempRow.Cells["< 100%"].Style.ForeColor = Color.FromArgb(255, 255, 255);
                _tempRow.Cells["100%"].Style.ForeColor = Color.FromArgb(255, 255, 255);
                _tempRow.Cells["150%"].Style.ForeColor = Color.FromArgb(255, 255, 255);
                _tempRow.Cells["200%"].Style.ForeColor = Color.FromArgb(255, 255, 255);
                _tempRow.Cells["250%"].Style.ForeColor = Color.FromArgb(255, 255, 255);
                _tempRow.Cells["> 300%"].Style.ForeColor = Color.FromArgb(255, 255, 255);
                if (_tempRow.Cells["< 100%"].Value.ToString() == "1")
                {
                    _tempRow.Cells["< 100%"].Style.BackColor = Color.FromArgb(230, 0, 0);
                    _tempRow.Cells["< 100%"].Style.ForeColor = Color.FromArgb(230, 0, 0);
                }
                if (_tempRow.Cells["100%"].Value.ToString() == "1")
                {
                    _tempRow.Cells["100%"].Style.BackColor = Color.FromArgb(230, 0, 0);
                    _tempRow.Cells["100%"].Style.ForeColor = Color.FromArgb(230, 0, 0);
                }
                if (_tempRow.Cells["150%"].Value.ToString() == "1")
                {
                    _tempRow.Cells["150%"].Style.BackColor = Color.FromArgb(230, 230, 0);
                    _tempRow.Cells["150%"].Style.ForeColor = Color.FromArgb(230, 230, 0);
                }
                if (_tempRow.Cells["200%"].Value.ToString() == "1")
                {
                    _tempRow.Cells["200%"].Style.BackColor = Color.FromArgb(230, 230, 0);
                    _tempRow.Cells["200%"].Style.ForeColor = Color.FromArgb(230, 230, 0);
                }
                if (_tempRow.Cells["250%"].Value.ToString() == "1")
                {
                    _tempRow.Cells["250%"].Style.BackColor = Color.FromArgb(0, 230, 0);
                    _tempRow.Cells["250%"].Style.ForeColor = Color.FromArgb(0, 230, 0);
                }
                if (_tempRow.Cells["> 300%"].Value.ToString() == "1")
                {
                    _tempRow.Cells["> 300%"].Style.BackColor = Color.FromArgb(0, 230, 0);
                    _tempRow.Cells["> 300%"].Style.ForeColor = Color.FromArgb(0, 230, 0);
                }
            }
        }

        private void buttonRekap_Click(object sender, EventArgs e)
        {
            _db = new DBConnect(textBox1.Text, comboBox1.Text);
            MySqlCommand _command = _db.OpenConnection().CreateCommand();
            showRekap(_command);
            _db.CloseConnection();
            _filename = "rekap_" + comboBox1.Text + "_" + DateTime.Now.ToString("d-M-yyyy") + ".xls";
            _allowEdit = false;
        }

        private void buttonStok_Click(object sender, EventArgs e)
        {
            _db = new DBConnect(textBox1.Text, comboBox1.Text);
            MySqlCommand _command = _db.OpenConnection().CreateCommand();
            showStok(_command);
            _db.CloseConnection();
            _filename = "stok_" + comboBox1.Text + "_" + DateTime.Now.ToString("d-M-yyyy") + ".xls";
            _allowEdit = false;
        }

        private void buttonSeverityLevel_Click(object sender, EventArgs e)
        {
            _db = new DBConnect(textBox1.Text, comboBox1.Text);
            MySqlCommand _command = _db.OpenConnection().CreateCommand();
            showLevel(_command);
            _db.CloseConnection();
            _filename = "stoklevel_" + comboBox1.Text + "_" + DateTime.Now.ToString("d-M-yyyy") + ".xls";
            _allowEdit = true;
        }

        private void dataGridView1_Sorted(object sender, EventArgs e)
        {
            changeLevelColour();
        }

        private void buttonExportToExcel_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "Excel Documents (*.xls)|*.xls";
            sfd.FileName = _filename;
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                //ToCsV(dataGridView1, @"c:\export.xls");
                Excel _excel = new Excel();
                _excel.ToCsV(dataGridView1, sfd.FileName); // Here dataGridview1 is your grid view name 
            } 
        }

        private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            if (_allowEdit == true)
            {
                MySqlCommand _cmd = _db.OpenConnection().CreateCommand();
                _cmd.CommandText = "UPDATE severity_level SET batas_aman = " + dataGridView1.Rows[e.RowIndex].Cells["Batas Aman"].Value.ToString() + " WHERE barang = '" + dataGridView1.Rows[e.RowIndex].Cells["Barang"].Value.ToString() + "'";
                _cmd.Prepare();
                _cmd.ExecuteNonQuery();
                _cmd.CommandText = "INSERT IGNORE severity_level (barang, batas_aman) VALUES (@barang, @batas_aman)";
                _cmd.Prepare();
                _cmd.Parameters.AddWithValue("@barang", dataGridView1.Rows[e.RowIndex].Cells["Barang"].Value.ToString());
                _cmd.Parameters.AddWithValue("@batas_aman", dataGridView1.Rows[e.RowIndex].Cells["Batas Aman"].Value.ToString());
                _cmd.ExecuteNonQuery();
                _db.CloseConnection();

            }
        }
    }
}
